import pandas as pd
import numpy as np
import os

output_dir = "老年体检_核查结果"
os.makedirs(output_dir, exist_ok=True)

print("【老年体检表核查】 - 2025.09.21")
print("功能说明:\n -1. 单表核查模式")
print(" -2. 使用极值判断规则")
print(" -3. 增加与居民档案的判断规则")
print(" -4. 将体检表分村")
print(" -5. 对核查后的数据进行统计")
print(" -6. 可视化显示核查结果")
print('-' * 50)
print('步骤1：对数据进行初步处理...')

df = pd.read_excel("体检明细表_公卫.xlsx", dtype={"个人健康档案号": str})
df["错误原因"] = ""

def process_data(row):
    errors = []

    # 1. 计算体检年龄
    try:
        id_card = str(row["身份证号"])
        birth_year = int(id_card[6:10])
        exam_date = str(row["体检日期"])
        exam_year = int(exam_date[:4])
        row["体检年龄"] = exam_year - birth_year
    except:
        row["体检年龄"] = -1 
        errors.append("体检年龄计算错误")
    
    # 2. 数值字段处理函数
    def process_numeric_field(value, field_name, require_int=False, allow_zero=True):
        if pd.isna(value):
            return -1, True
        
        try:
            num_val = float(value)
            if require_int and not num_val.is_integer():
                return -1, True
            if not allow_zero and num_val == 0:
                return -1, True
            return int(num_val) if require_int else num_val, False
        except (ValueError, TypeError):
            return -1, True
    
    # 3. 处理整数字段
    int_fields = ["脉率", "呼吸频率", "左收缩压", "左舒张压", "右收缩压", "右舒张压"]
    for field in int_fields:
        row[field], has_error = process_numeric_field(row[field], field, require_int=True)
        if has_error:
            errors.append(field)
    
    # 4. 处理浮点数字段
    float_fields = ["体温", "身高", "体重", "腰围", "体质指数", "心脏|心率"]
    for field in float_fields:
        row[field], has_error = process_numeric_field(row[field], field)
        if has_error:
            errors.append(field)
    
    # 5. 处理体育锻炼相关字段
    if not pd.isna(row["体育锻炼|频率"]) and row["体育锻炼|频率"] != "不锻炼":
        # 分钟/次需要整数
        row["体育锻炼|分钟/次"], has_error = process_numeric_field(row["体育锻炼|分钟/次"], "体育锻炼|分钟/次", require_int=True)
        if has_error:
            errors.append("体育锻炼|分钟/次")
        
        # 坚持年数可以是小数
        row["体育锻炼|坚持年数"], has_error = process_numeric_field( row["体育锻炼|坚持年数"], "体育锻炼|坚持年数" )
        if has_error:
            errors.append("体育锻炼|坚持年数")
    
    # 6. 处理吸烟相关字段
    smoking_status = row["吸烟情况|状况"]
    if smoking_status == "吸烟":
        row["吸烟情况|日吸烟量"], has_error = process_numeric_field(  row["吸烟情况|日吸烟量"], "吸烟情况|日吸烟量", require_int=True, allow_zero=False)
        if has_error:
            errors.append("吸烟情况|日吸烟量")
    
    if smoking_status in ["吸烟", "已戒烟"]:
        row["吸烟情况|开始吸烟年龄"], has_error = process_numeric_field( row["吸烟情况|开始吸烟年龄"], "吸烟情况|开始吸烟年龄", require_int=True )
        if has_error:
            errors.append("吸烟情况|开始吸烟年龄")
    
    if smoking_status == "已戒烟":
        row["吸烟情况|戒烟年龄"], has_error = process_numeric_field( row["吸烟情况|戒烟年龄"], "吸烟情况|戒烟年龄", require_int=True )
        if has_error:
            errors.append("吸烟情况|戒烟年龄")
    
    # 7. 处理饮酒相关字段
    drinking_freq = row["饮酒情况|频率"]
    if drinking_freq != "从不" and not pd.isna(drinking_freq):
        row["饮酒情况|日饮酒量"], has_error = process_numeric_field( row["饮酒情况|日饮酒量"], "饮酒情况|日饮酒量" )
        if has_error:
            errors.append("饮酒情况|日饮酒量")
        
        row["饮酒情况|开始饮酒年龄"], has_error = process_numeric_field( row["饮酒情况|开始饮酒年龄"], "饮酒情况|开始饮酒年龄", require_int=True)
        if has_error:
            errors.append("饮酒情况|开始饮酒年龄")
    
    if row["饮酒情况|是否戒酒"] == "已戒酒":
        row["饮酒情况|戒酒年龄"], has_error = process_numeric_field( row["饮酒情况|戒酒年龄"], "饮酒情况|戒酒年龄", require_int=True )
        if has_error:
            errors.append("饮酒情况|戒酒年龄")
    
    # 8. 处理视力字段
    vision_fields = ["左眼视力", "右眼视力", "左眼矫正", "右眼矫正"]
    for field in vision_fields:
        row[field], has_error = process_numeric_field(row[field], field)
        if has_error:
            errors.append(field)
    
    # 9. 处理不能为空的文本字段
    text_fields = ["体检医生", "症状", "体育锻炼|频率", "饮食习惯",  "吸烟情况|状况", "饮酒情况|频率", "职业危害|是否接触"]
    for field in text_fields:
        if pd.isna(row[field]):
            row[field] = 'NaN'
            errors.append(field)
    
    # 10. 汇总错误信息
    row["错误原因"] = ",".join(errors) if errors else ""
    
    return row

df = df.apply(process_data, axis=1)
output_path = os.path.join(output_dir, "体检明细表_类型已处理.xlsx")
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    df.to_excel(writer, index=False)
    worksheet = writer.sheets['Sheet1']
    for idx in range(1, len(df) + 2):  # 包含标题行
        cell = worksheet.cell(row=idx, column=df.columns.get_loc("个人健康档案号") + 1)
        cell.number_format = '@'  # 文本格式

print(f"  处理完成，结果已保存至: {output_path}")
###################################################################################
import os
import pandas as pd
import numpy as np
import re
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, colors
from tqdm import tqdm
import time
import datetime
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)

def print_header(title): print("="*60 + f"\n{title.upper()}...")
def print_step(step_num, step_desc): print(f"步骤{step_num}: {step_desc}...")
def print_success(message): print(f"  ✅ {message}")

def calculate_age(id_card, exam_date):
    exam_date = pd.to_datetime(exam_date)
    birth_date = datetime.datetime.strptime(id_card[6:14], '%Y%m%d')
    age = exam_date.year - birth_date.year - ((exam_date.month, exam_date.day) < (birth_date.month, birth_date.day))
    return age

def calculate_age_2(id_card, exam_date):
    exam_date = pd.to_datetime(exam_date)
    birth_date = datetime.datetime.strptime(id_card[6:14], '%Y%m%d')
    age = exam_date.year - birth_date.year
    return age

def validate_phone_number(phone):
    if pd.isna(phone) or not phone:
        return False
    
    phone_str = str(phone).strip().replace(" ", "").replace("-", "").replace(" ", "")
    
    if not re.match(r'^[0-9\-]+$', phone_str):
        return False
    
    if phone_str.startswith('0513'):
        if '-' in phone_str:
            return len(phone_str) == 13
        else:
            return len(phone_str) == 12
    
    return len(phone_str) in [8, 11] and phone_str.isdigit()

def check_physical_exam_data(df):
    print_step(2, "开始核查数据")
    df['积分分值'] = 0
    df['体检表是否规范'] = ''
    df['核查结果汇总'] = ''
    df['核查现存健康问题'] = ''
    df['核查健康评价'] = ''
    df['核查健康指导'] = ''
    df['核查其他空错项'] = ''

    disease_cols = ['异常疾病|异常1', '异常疾病|异常2', '异常疾病|异常3', '异常疾病|异常4']

    for idx, row in tqdm(df.iterrows(), total=len(df), desc="  进度"):
        results = []
        health_problem_issues = []  # 核查现存健康问题
        health_eval_issues = []     # 核查健康评价
        health_guide_issues = []    # 核查健康指导
        other_issues = []           # 核查其他空错项
        score_deduction = 0         # 积分扣除值

        skip_checks = pd.isna(row.get('血常规|血红蛋白'))

        id_card = row.get('身份证号')
        exam_date = row.get('体检日期')
        age = calculate_age(id_card, exam_date) if id_card and exam_date else None
        age_2 = calculate_age_2(id_card, exam_date) if id_card and exam_date else None
        
        other_risk = str(row.get('其他危险因素', ''))
        risk_factors = str(row.get('危险因素控制', ''))
        drinking_freq = str(row.get('饮酒情况|频率', ''))
        quit_drinking = str(row.get('饮酒情况|是否戒酒', ''))
        bmi = row.get('体质指数')
        gender = row.get('性别')
        waist_val = row.get('腰围', 0)

        #--------------------核查部分代码---------------------
        # 1. -----------------------------------------体检医生，症状，联系电话
        doctor_tijian = str(row.get('体检医生', ''))
        if doctor_tijian == 'NaN' or pd.isna(doctor_tijian):
            results.append("体检医生")
            other_issues.append("体检医生空")
            score_deduction += 0.1

        symptoms = str(row.get('症状', ''))
        is_empty = symptoms.strip() in ['', 'nan', 'None','NaN']
        if('无症状' in symptoms and symptoms != '无症状,')or is_empty :
            results.append("症状")
            other_issues.append("症状逻辑错误或空")
            score_deduction += 0.5
        
        if not validate_phone_number(row['联系电话']):
            results.append('联系电话')
            other_issues.append("联系电话不规范")
            score_deduction += 0.1

        # 2. -----------------------------------------体温,脉率,呼吸检查
        temp_val = float(row.get('体温','-1'))
        if temp_val < 35 or temp_val > 38:
            results.append("体温")
            other_issues.append("体温异常或空")
            score_deduction += 1

        pulse_val = float(row.get('脉率','-1'))
        if pulse_val < 35 or pulse_val > 170:
            results.append("脉率")
            other_issues.append("脉率异常或空")
            score_deduction += 1

        resp_val = float(row.get('呼吸频率','-1'))
        if resp_val < 12 or resp_val > 30:
            results.append("呼吸")
            other_issues.append("呼吸频率异常或空")
            score_deduction += 1

        # 3. -----------------------------------------血压检查
        ls = row.get('左收缩压')
        ld = row.get('左舒张压')
        rs = row.get('右收缩压')
        rd = row.get('右舒张压')
        bp_issues = False
        if ls <= ld or rs <= rd:
            bp_issues = True
        elif ls < 70 or ls > 240 or rs < 70 or rs > 240:
            bp_issues = True
        elif ld < 35 or ld > 135 or rd < 35 or rd > 135:
            bp_issues = True

        if bp_issues:
            results.append("血压")
            other_issues.append("血压值异常")
            score_deduction += 1

        # 4. -----------------------------------------身高，体重,目标体重,体重评价
        height_val = float(row.get('身高','-1'))
        bmi_val = float(bmi)
        if height_val < 90 or height_val > 200:
            results.append("身高")
            other_issues.append("身高异常或空")
            score_deduction += 1

        weight_val = float(row.get('体重','-1'))
        if weight_val < 30 or weight_val > 150:
            results.append("体重")
            other_issues.append("体重异常或空")
            score_deduction += 1
        
        def contains_weight_keyword(keywords):
            return any(any(kw in str(row.get(col, '')) for kw in keywords) for col in disease_cols if col in df.columns)

        if bmi_val < 18.5:  
            if bmi_val > 0: 
                if not contains_weight_keyword(['体重', '过轻','消瘦']):
                    results.append("少体指评价")
                    health_eval_issues.append("体重偏轻未评价")
                    score_deduction += 5

                if "增" not in other_risk:
                    results.append("少增重")
                    health_guide_issues.append("少增重建议")
                    score_deduction += 5
            else:
                results.append("体指空")
                other_issues.append("体指空")
                score_deduction += 1  
        elif bmi_val >= 24:     
            if 24 <= bmi_val < 28:
                if not contains_weight_keyword(['超重', '偏胖']):
                    results.append("少体指评价")
                    health_eval_issues.append("超重未评价")
                    score_deduction += 5
            else: #>= 28:
                if not contains_weight_keyword(['肥胖']):
                    results.append("少体指评价")
                    health_eval_issues.append("肥胖未评价")
                    score_deduction += 5

            if ("减体重" not in risk_factors) and ("减体重" not in other_risk):
                results.append("少减重")
                health_guide_issues.append("少减重建议")
                score_deduction += 3
                
            target_weight_val = float(row.get('目标体重(KG)','-1'))
            weight_val =float(row.get('体重','-1'))

            if ((weight_val <= 50) and (weight_val - target_weight_val < 1)) or(weight_val - target_weight_val < 2):
                results.append("目标体重")
                health_guide_issues.append("目标体重不合理")
                score_deduction += 0.5  
        else:  # 18.5 <= BMI < 24
            if contains_weight_keyword(['消瘦', '超重', '偏胖']):
                results.append("多体指评价")
                health_eval_issues.append("正常体重错误评价")
                score_deduction += 3

            if ("减体重" in risk_factors) or ("减体重" in other_risk):
                results.append("多减重")
                health_guide_issues.append("多减重建议")
                score_deduction += 3

        # 5. -----------------------------------------腰围，腹型肥胖，减腰围
        waist_val = float(row.get('腰围','-1'))
        if waist_val < 45 or waist_val > 150:
            results.append("腰围")
            other_issues.append("腰围异常或空")
            score_deduction += 1
            
        def contains_obesity_keyword():
            keywords = ['腹型肥胖', '腰围', '性肥胖']
            return any(any(kw in str(row.get(col, '')) for kw in keywords) for col in disease_cols if col in df.columns)
             
        if gender == '男':
            if waist_val >= 90:
                if ("减腰围" not in risk_factors) and ("减腰围" not in other_risk):
                    results.append("少减腰围")
                    health_guide_issues.append("少减腰围建议")
                    score_deduction += 1
                
                if not contains_obesity_keyword():
                    results.append("少腹型肥胖")
                    health_eval_issues.append("少腹型肥胖评价")
                    score_deduction += 5
            else:
                if ("减腰围" in risk_factors) or ("减腰围" in other_risk):
                    results.append("多减腰围")
                    health_guide_issues.append("多减腰围建议")
                    score_deduction += 1
        elif gender == '女':
            if waist_val >= 85:
                if ("减腰围" not in risk_factors) and ("减腰围" not in other_risk):
                    results.append("少减腰围")
                    health_guide_issues.append("少减腰围建议")
                    score_deduction += 1
                
                if not contains_obesity_keyword():
                    results.append("少腹型肥胖")
                    health_eval_issues.append("少腹型肥胖评价")
                    score_deduction += 5
            else:
                if ("减腰围" in risk_factors) or ("减腰围" in other_risk):
                    results.append("多减腰围")
                    health_guide_issues.append("多减腰围建议")
                    score_deduction += 1

        # 6. -----------------------------------------体育锻炼，危险因素
        exercise_freq = str(row.get('体育锻炼|频率', ''))
        minutes_per = float(row.get('体育锻炼|分钟/次','-1'))
        years_type = float(row.get('体育锻炼|坚持年数','-1'))
        exercise_type = str(row.get('体育锻炼|锻炼方式'))
        cleaned_type = exercise_type.strip()

        if exercise_freq in ['每天', '每周一次以上', '偶尔']:
            if (minutes_per < 10):
                results.append("锻炼")
                other_issues.append("锻炼分钟缺失或偏少")
                score_deduction += 0.5
            if years_type <= 0:
                results.append("锻炼")
                other_issues.append("坚持年数缺失")
                score_deduction += 0.5
            if cleaned_type in ['', 'nan', 'None'] or not any(char.isalpha() for char in cleaned_type):
                results.append("锻炼")
                other_issues.append("锻炼方式缺失或错误")
                score_deduction += 0.5
            elif len(cleaned_type) == 1 and '\u4e00' <= cleaned_type <= '\u9fff':
                results.append("锻炼")
                other_issues.append("锻炼方式缺失或错误")
                score_deduction += 0.5
            else:
                for char in cleaned_type:
                    allowed_chars = " \u3000,;，；、." #中英文空格，中英文逗号，中英文分号，中英文顿号
                    if not ('\u4e00' <= char <= '\u9fff' or char in allowed_chars):
                        results.append("锻炼")
                        other_issues.append("锻炼方式缺失或错误")
                        score_deduction += 0.5
                        break

        if "每天" not in exercise_freq:
            if "锻炼" not in risk_factors:
                results.append("少锻炼")
                health_guide_issues.append("少锻炼建议")
                score_deduction += 3
        else:
            if "锻炼" in risk_factors:
                results.append("多锻炼")
                health_guide_issues.append("多锻炼建议")
                score_deduction += 3
        
        if(exercise_freq =='NaN') or (exercise_freq =='不锻炼'):
            if (minutes_per > 0) :
                results.append("锻炼")
                other_issues.append("锻炼逻辑错误")
                score_deduction += 0.5
            if (years_type > 0):
                results.append("锻炼")
                other_issues.append("锻炼逻辑错误")
                score_deduction += 0.5
            if cleaned_type not in ['', 'nan', 'None'] and any(char.isalpha() for char in cleaned_type):
                results.append("锻炼")
                other_issues.append("多锻炼方式")
                score_deduction += 0.5
        # 7. -----------------------------------------吸烟,量，年龄，危险因素
        smoking = str(row.get('吸烟情况|状况', ''))
        start_smoke_age = float(row.get('吸烟情况|开始吸烟年龄','-1'))
        daily_smoke = float(row.get('吸烟情况|日吸烟量','-1'))
        quit_smoke_age = float(row.get('吸烟情况|戒烟年龄','-1'))
        if smoking == "吸烟":
            if start_smoke_age < 10:
                results.append("吸烟")
                other_issues.append("吸烟年龄异常")
                score_deduction += 0.5
            if daily_smoke <= 0:
                results.append("吸烟")
                other_issues.append("日吸烟量缺失")
                score_deduction += 0.5    

            if "戒烟" not in risk_factors:
                results.append("少戒烟")
                health_guide_issues.append("少戒烟建议")
                score_deduction += 3      
        else:
            if smoking == '已戒烟':
                if (start_smoke_age < 10):
                    results.append("吸烟")
                    other_issues.append("吸烟年龄异常")
                    score_deduction += 0.5
                if (quit_smoke_age < 13)or (quit_smoke_age-start_smoke_age < 0):
                    results.append("吸烟")
                    other_issues.append("戒烟年龄异常")
                    score_deduction += 0.5

            if "戒烟" in risk_factors:
                results.append("多戒烟")
                health_guide_issues.append("多戒烟建议")
                score_deduction += 3

        if smoking=='从不吸烟':
            if (start_smoke_age > 0) or (daily_smoke > 0) or (quit_smoke_age > 0):
                results.append("吸烟")
                other_issues.append("吸烟逻辑错误")
                score_deduction += 0.5
        # 8. -----------------------------------------饮酒，年龄，量，危险因素
        drunk_in_year = row.get('饮酒情况|年内醉酒')
        drink_type = row.get('饮酒情况|饮酒种类')
        daily_alcohol = float(row.get('饮酒情况|日饮酒量','-1'))
        quit_drink_age = float(row.get('饮酒情况|戒酒年龄','-1'))
        start_drink_age = float(row.get('饮酒情况|开始饮酒年龄','-1'))
        if drinking_freq in ['每天', '偶尔', '经常']:
            if daily_alcohol <= 0:
                results.append("饮酒")
                other_issues.append("日饮酒量缺失")
                score_deduction += 0.5

            if start_drink_age <= 14:
                results.append("饮酒")
                other_issues.append("开始饮酒年龄缺失")
                score_deduction += 0.5
            
            if quit_drinking == '已戒酒':
                if (quit_drink_age < 15)or(quit_drink_age-start_drink_age<0):
                    results.append("饮酒")
                    other_issues.append("戒酒年龄异常")
                    score_deduction += 0.5

            if pd.isna(drunk_in_year):
                results.append("饮酒")
                other_issues.append("年内醉酒缺失")
                score_deduction += 0.5

            if pd.isna(drink_type):
                results.append("饮酒")
                other_issues.append("饮酒种类缺失")
                score_deduction += 0.5
        else:
            if (daily_alcohol > 0) or (start_drink_age > 0) or (quit_drink_age > 0) or(drunk_in_year=='是') or ( not pd.isna(drink_type)):
                    results.append("饮酒")
                    other_issues.append("饮酒逻辑错误")
                    score_deduction += 0.5

        if drinking_freq != "从不":
            if quit_drinking == "未戒酒":
                if ("健康饮酒" not in risk_factors) and (drinking_freq != "偶尔"):
                    results.append("少健康饮酒")
                    health_guide_issues.append("少健康饮酒建议")
                    score_deduction += 3
            elif quit_drinking == "已戒酒":
                if "健康饮酒" in risk_factors:
                    results.append("多健康饮酒")
                    health_guide_issues.append("多健康饮酒建议")
                    score_deduction += 3
        else:
            if "健康饮酒" in risk_factors:
                results.append("多健康饮酒")
                health_guide_issues.append("多健康饮酒建议")
                score_deduction += 3
                
        # 9. -----------------------------------------饮食习惯
        diet = str(row.get('饮食习惯', ''))
        diet_options = ["荤素均衡", "素食为主", "荤食为主"]
        count = sum(1 for option in diet_options if option in diet)
        if count != 1:
            results.append("饮食")
            other_issues.append("饮食习惯选择错误")
            score_deduction += 1

        if any(keyword in diet for keyword in ["素食", "荤食", "嗜"]):
            if "饮食" not in risk_factors:
                results.append("饮食少评价")
                health_guide_issues.append("饮食危险因素未评价")
                score_deduction += 3

        if diet == '荤素均衡,' and "饮食" in risk_factors:
            results.append("饮食多评价")
            health_guide_issues.append("多饮食指导")
            score_deduction += 3

        # 10. -----------------------------------------视力检查
        eye_cols = ['左眼视力', '左眼矫正', '右眼视力', '右眼矫正']
        eye_issue = False
        if (pd.isna(row.get('左眼视力')) and pd.isna(row.get('左眼矫正'))) or (pd.isna(row.get('右眼视力')) and pd.isna(row.get('右眼矫正'))):
            eye_issue = True

        for col in eye_cols:
            if col in df.columns:
                val = row.get(col)
                if not pd.isna(val):
                    val_num = float(val)
                    if val_num != 0 and val_num != -1 and (val_num < 4.0 or val_num > 5.3):
                        eye_issue = True

        if eye_issue:
            results.append("视力")
            other_issues.append("视力检查异常")
            score_deduction += 1

        # 11. -----------------------------------------心脉率
        pr_val = float(row.get('脉率','-1'))
        hr_val = float(row.get('心脏|心率','-1'))
        has_fibrillation = False
        for col in disease_cols:
            if col in df.columns:
                disease_text = str(row.get(col, ''))
                if '颤' in disease_text or '扑' in disease_text:
                    has_fibrillation = True
                    break
        
        if hr_val < 35 or hr_val > 170 or hr_val != int(hr_val):
                results.append("心率")
                health_eval_issues.append("心率异常")
                score_deduction += 1
        
        if hr_val < 60 and pr_val < 60:
                has_bradycardia = any(
                    "过缓" in str(row.get(col, '')) for col in disease_cols if col in df.columns)
                if not has_bradycardia:
                    results.append("无过缓")
                    health_eval_issues.append("心率过缓未评价")
                    score_deduction += 0.5

        if not has_fibrillation:
            if abs(hr_val - pr_val) >= 15:
                results.append("脉心率差")
                health_eval_issues.append("心率脉率差异过大")
                score_deduction += 0.5

        # 12. -----------------------------------------老年评估，职业危害
        if  age >= 65:
            elder_assess = row.get('老年人自我评估')
            cognitive_assess = row.get('老年人生活自理评估')
            if pd.isna(elder_assess) or pd.isna(cognitive_assess):
                results.append("老年评估")
                other_issues.append("老年评估缺失")
                score_deduction += 1

        if pd.isna(row.get('职业危害|是否接触')):
            results.append("职业危害")
            other_issues.append("职业危害缺失")
            score_deduction += 0.5

        # 13. -----------------------------------------口腔科，外科，内科
        oral_cols = ['口腔|口唇', '口腔|咽部', '听力']
        oral_issues = []
        if any(pd.isna(row.get(col)) for col in oral_cols if col in df.columns):
            oral_issues.append("口腔科检查缺失")
            score_deduction += 1
        if '口腔|齿列' in df.columns:
            oral_condition = str(row.get('口腔|齿列')).strip()
            if '正常' in oral_condition and oral_condition != '正常':
                oral_issues.append("齿列错误")
                score_deduction += 0.5
        if oral_issues:
            results.append("口腔科")
            other_issues.extend(oral_issues)

        surgery_cols = ['运动功能', '皮肤', '巩膜']
        if any(pd.isna(row.get(col)) for col in surgery_cols if col in df.columns):
            results.append("外科")
            other_issues.append("外科检查缺失")
            score_deduction += 1

        internal_cols = ['肺|桶状胸', '肺|呼吸音', '肺|罗音', '心脏|心律', '心脏|杂音','腹部|压  痛', '腹部|包  块',
                          '腹部|肝  大', '腹部|脾  大', '腹部|移动性浊音', '下肢水肿', '足背动脉搏动']
        if any(pd.isna(row.get(col)) for col in internal_cols if col in df.columns):
            results.append("内科")
            other_issues.append("内科检查缺失")
            score_deduction += 1

        # 14. -----------------------------------------辅助检查
        if not skip_checks:
            blood_cols = ['血常规|血红蛋白', '血常规|白细胞', '血常规|血小板']
            existing_cols = [col for col in blood_cols if col in df.columns]
            if existing_cols:
                has_invalid = any(pd.isna(row[col]) or row[col] < 0 for col in existing_cols)    
                if has_invalid:
                    results.append("血Rt")
                    health_eval_issues.append("血常规缺失或异常值")
                    score_deduction += 10

            urine_cols = ['尿常规|尿蛋白', '尿常规|尿糖', '尿常规|尿酮体', '尿常规|尿潜血']
            if any(pd.isna(row.get(col)) for col in urine_cols if col in df.columns):
                results.append("尿Rt")
                health_eval_issues.append("尿常规缺失")
                score_deduction += 10

            liver_cols = ['肝功能|血清谷丙转氨酶', '肝功能|血清谷草转氨酶', '肝功能|总胆红素']
            existing_cols = [col for col in liver_cols if col in df.columns]
            if existing_cols:
                has_invalid = any(pd.isna(row[col]) or row[col] < 0 for col in existing_cols)    
                if has_invalid:
                    results.append("肝功")
                    health_eval_issues.append("肝功能缺失")
                    score_deduction += 10

            lipid_cols = [ '血脂|总胆固醇', '血脂|甘油三酯','血脂|血清低密度脂蛋白胆固醇', '血脂|血清高密度脂蛋白胆固醇']
            existing_cols = [col for col in lipid_cols if col in df.columns]
            if existing_cols:
                has_invalid = any(pd.isna(row[col]) or row[col] < 0 for col in existing_cols)    
                if has_invalid:
                    results.append("血脂")
                    health_eval_issues.append("血脂缺失")
                    score_deduction += 10

            renal_cols = ['肾功能|血清肌酐', '肾功能|血 尿 素']     
            existing_cols = [col for col in renal_cols if col in df.columns]
            if existing_cols:
                has_invalid = any(pd.isna(row[col]) or row[col] < 0 for col in existing_cols)    
                if has_invalid:
                    results.append("肾功")
                    health_eval_issues.append("肾功能缺失")
                    score_deduction += 10

            exam_cols = ['心电图', '腹部B超']
            if any(pd.isna(row.get(col)) for col in exam_cols if col in df.columns):
                results.append("心电B超")
                health_eval_issues.append("检查项缺失")
                score_deduction += 10
            
            hba1c_value = float(row.get('糖化血红蛋白','-9999'))
            if hba1c_value == -9999 :
                results.append("糖化空")
                health_eval_issues.append("糖化血红蛋白缺失")
            else:
                has_diabetes = '糖' in str(row.get('其他系统疾病其它', ''))     
                has_hba1c_comment = False
                for col in disease_cols:
                    if col in df.columns:
                        disease_text = str(row.get(col, ''))
                        if '糖化' in disease_text or '血糖控制不满意' in disease_text or'受损' in disease_text or '血糖控制不达标' in disease_text:
                            has_hba1c_comment = True
                            break
                
                threshold = 7.0 if has_diabetes else 6.5
                
                if hba1c_value >= threshold and not has_hba1c_comment:
                    results.append("糖化未评价")
                    health_eval_issues.append("糖化血红蛋白异常未评价")
                    score_deduction += 1
        else:
            results.append("无法核查")
            other_issues.append("未核查体检表")
            score_deduction += 20

        # 15. -----------------------------------------其他系统
        other_disease = row.get('其他系统疾病')
        other_disease_other = row.get('其他系统疾病其它')
        if other_disease == '有' and pd.isna(other_disease_other):
            results.append("既往史")
            health_problem_issues.append("其他系统疾病缺失")
            score_deduction += 1
            
        other_disease_2 = str(row.get('其他系统疾病其它', ''))
        health_guide = str(row.get('健康指导', ''))
        if "血压" in other_disease_2 or "糖" in other_disease_2 or "阻肺" in other_disease_2 or \
            "慢支" in other_disease_2 or "慢性支" in other_disease_2 or "肺气肿" in other_disease_2:
            if "纳入" not in health_guide:
                results.append("健康指导")
                health_guide_issues.append("少纳入管理")
                score_deduction += 5
        else:
            if "纳入" in health_guide:
                results.append("健康指导")
                health_guide_issues.append("多纳入管理")
                score_deduction += 5

        # 16. -----------------------------------------心电图异常
        ecg_value = row.get('心电图异常')
        if pd.isna(ecg_value) or ecg_value is None or str(ecg_value).strip() == '':
            ecg_abnormal = ''
        else:
            ecg_abnormal = re.sub(r'\s+', ' ', str(ecg_value)).strip() 

        if ecg_abnormal and ecg_abnormal != '窦性心律':
            ecg_keywords = [ '不齐', '过缓', '过速', '阻滞', '颤', '早', '高电压', '低电压', '电轴', '肥', 'T', '梗', '起']

            keyword_found = False
            keyword_found_two = False

            for keyword in ecg_keywords:
                if keyword in ecg_abnormal:
                    keyword_found_two = True
                    for col in disease_cols:
                        if col in df.columns:
                            disease_value = str(row.get(col, ''))
                            cleaned_disease = re.sub(r'\s+', ' ', disease_value).strip()
                            if keyword in cleaned_disease:
                                keyword_found = True
                                break
                    if keyword_found:
                        break

            if not keyword_found and keyword_found_two:
                if "心电图异常评价" not in results:
                    results.append("心电图漏评价")
                    health_eval_issues.append("心电图漏评价")
                    score_deduction += 0.3

        # 17. -----------------------------------------腹部B超
        ultrasound_value = row.get('腹部B超其它')
        if pd.isna(ultrasound_value) or ultrasound_value is None or str(ultrasound_value).strip() == '':
            ultrasound_other = ''
        else:
            ultrasound_other = re.sub(r'\s+', ' ', str(ultrasound_value)).strip()

        if ultrasound_other:
            if "未见占位" in ultrasound_other:
                ultrasound_keywords = ['结石', '切除', '肿大', '水', '囊肿', '脂肪肝', '息肉', '血管瘤', '回声', '钙', '胆囊炎', '硬化']
            else:
                ultrasound_keywords = ['结石', '切除', '占位', '肿大', '水', '囊肿', '显示', 
                                        '脂肪肝', '息肉', '血管瘤', '回声', '钙', '胆囊炎', '硬化']

            keyword_found = False
            keyword_found_two = False

            for keyword in ultrasound_keywords:
                if keyword in ultrasound_other:
                    keyword_found_two = True
                    # 检查四个异常疾病列是否包含此关键字
                    for col in disease_cols:
                        if col in df.columns:
                            disease_value = str(row.get(col, ''))
                            cleaned_disease = re.sub(r'\s+', ' ', disease_value).strip()
                            if keyword in cleaned_disease:
                                keyword_found = True
                                break
                    if keyword_found:
                        break

            if not keyword_found and keyword_found_two:
                if "腹部B超评价" not in results:
                    results.append("B超漏评价")
                    health_eval_issues.append("B超漏评价")
                    score_deduction += 0.3
 
        # 18. -----------------------------------------血压核查规则和血压评价
        other_diseases = str(row.get('其他系统疾病其它', '')).strip()
        left_systolic = row.get('左收缩压')
        left_diastolic = row.get('左舒张压')
        right_systolic = row.get('右收缩压')
        right_diastolic = row.get('右舒张压')
        bp_values = [left_systolic, left_diastolic, right_systolic, right_diastolic]
        bp_values_valid = all(pd.notna(bp) and isinstance(bp, (int, float))for bp in bp_values)

        if bp_values_valid:
            # 规则1：非血压
            if '血压' not in other_diseases:
                bp_critical=(left_systolic >= 180 or left_diastolic >= 110 or right_systolic >= 180 or right_diastolic >= 110)
                if bp_critical:
                    contains_critical = any('危急值' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                            for col in disease_cols if col in df.columns)
                    if not contains_critical:
                        results.append("血压危急漏评价")
                        health_eval_issues.append("血压危急漏评价")
                        score_deduction += 5
                elif (left_systolic >= 140 or  left_diastolic >= 90 or  right_systolic >= 140 or right_diastolic >= 90):
                    contains_high = any('血压偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                        for col in disease_cols if col in df.columns )
                    if not contains_high:
                        results.append("血压少评价")
                        health_eval_issues.append("血压少评价")
                        score_deduction += 5
            else: # 血压人群
                bp_unsatisfactory = (left_systolic >= 150 or left_diastolic >= 90 or right_systolic >= 150 or right_diastolic >= 90)

                if bp_unsatisfactory:
                    contains_unsatisfactory = any(
                        '血压控制不满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip() or
                        '血压控制不达标' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                        for col in disease_cols if col in df.columns)
                    if not contains_unsatisfactory:
                        results.append("血压控制不满意/不达标漏评价")
                        health_eval_issues.append("血压控制不满意/不达标漏评价")
                        score_deduction += 5

                bp_satisfactory = (left_systolic < 150 and left_diastolic < 90 and right_systolic < 150 and right_diastolic < 90)

                if bp_satisfactory:
                    contains_satisfactory = any('血压控制满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                                for col in disease_cols if col in df.columns)
                    if contains_satisfactory:
                        results.append("血压控制满意多评价")
                        health_eval_issues.append("血压控制满意多评价")
                        score_deduction += 5

                    contains_satisfactory = any('血压偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                                for col in disease_cols if col in df.columns)
                    if contains_satisfactory:
                        results.append("血压多评价")
                        health_eval_issues.append("范围内血压多评价")
                        score_deduction += 5
                        
        # 19. -----------------------------------------血糖,血糖评价
        crowd_label = str(row.get('其他系统疾病其它', '')).strip()
        fasting_bs = row.get('空腹血糖')
        if '糖' not in crowd_label: #非糖尿病人群
            if 6.1 <= fasting_bs < 7:  #（6.1, 7)
                contains_damaged = any('受损' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                        for col in disease_cols if col in df.columns )
                if not contains_damaged:
                    results.append("空腹血糖受损漏评价")
                    health_eval_issues.append("空腹血糖受损漏评价")
                    score_deduction += 5
            if fasting_bs >= 7: #≥7
                contains_high = any('糖偏高' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                    for col in disease_cols if col in df.columns )
                if not contains_high:
                    results.append("血糖偏高漏评价")
                    health_eval_issues.append("血糖偏高漏评价")
                    score_deduction += 5
            if fasting_bs <= 3.9 or fasting_bs >= 16.7:  #(≤3.9或≥16.7)
                contains_critical = any('糖危急值' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                        for col in disease_cols if col in df.columns
                                        )
                if not contains_critical:
                    results.append("血糖危急值漏评价")
                    health_eval_issues.append("血糖危急值漏评价")
                    score_deduction += 5
        else: # 糖尿病人群
            if fasting_bs >= 7 or fasting_bs <= 3.9:
                contains_unsatisfactory = any(
                    '血糖控制不满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip() or
                    '血糖控制不达标' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                    for col in disease_cols if col in df.columns)
                if not contains_unsatisfactory:
                    results.append("血糖控制不满意/不达标漏评价")
                    health_eval_issues.append("血糖控制不满意/不达标漏评价")
                    score_deduction += 5

            if 3.9 < fasting_bs < 7:
                contains_satisfactory = any('血糖控制满意' in re.sub(r'\s+', ' ', str(row.get(col, ''))).strip()
                                            for col in disease_cols if col in df.columns)
                if contains_satisfactory:
                    results.append("血糖控制满意多评价")
                    health_eval_issues.append("血糖控制满意多评价")
                    score_deduction += 5
                        
        if not pd.isna(fasting_bs) and 3.9 <= fasting_bs < 6.1:  # 血糖多评价
            contains_blood_sugar = any(keyword in str(row.get(col, ''))  for keyword in ['血糖偏高', '血糖控制不满意','血糖受损']
                for col in disease_cols if col in df.columns)
            
            if contains_blood_sugar:
                results.append("血糖多评价")
                health_eval_issues.append("血糖多评价")
                score_deduction += 5
        
        # 20. -----------------------------------------防跌倒检查
        if age_2 >= 70:
            if "防跌倒" not in other_risk:
                results.append("少防跌倒")
                health_guide_issues.append("防跌倒建议缺失")
                score_deduction += 0.3
        elif "防跌倒" in other_risk:
            results.append("多防跌倒")
            health_guide_issues.append("多防跌倒建议")
            score_deduction += 0.3
        
        # 21. -----------------------------------------标签核查
        population_tag = str(row.get('人群标签', ''))
        other_disease_other = str(row.get('其他系统疾病其它', ''))

        if population_tag:
            if '高' in population_tag and '血压' not in other_disease_other:
                results.append("现存健康问题")
                health_problem_issues.append("高血压现存主要健康问题")
                score_deduction += 0.5

            if '糖' in population_tag and '糖' not in other_disease_other:
                results.append("现存健康问题")
                health_problem_issues.append("糖尿病现存主要健康问题")
                score_deduction += 0.5
        # 22. -----------------------------------------指导空核查
        risk_factors = row.get('危险因素控制')
        risk_factors_two = row.get('其他危险因素')

        is_risk_factors_empty = pd.isna(risk_factors) or (isinstance(risk_factors, str) and risk_factors.strip() == '')
        is_risk_factors_two_empty = pd.isna(risk_factors_two) or (isinstance(risk_factors_two, str) and risk_factors_two.strip() == '')
        
        if is_risk_factors_empty and is_risk_factors_two_empty:
            results.append("危险因素控制空")
            health_guide_issues.append("危险因素控制空")
            score_deduction += 0.5
        
        # 23. -----------------------------------------乱码
        abnormal_content = str(row.get('异常疾病|异常1', '')).strip()
        keywords = ['尿蛋白:', '尿糖:', '尿酮体:', '尿潜血:']
        if all(keyword in abnormal_content for keyword in keywords):
            results.append("评价乱码")
            health_eval_issues.append("健康评价混乱")
            score_deduction += 1.0
        #--------------------核查部分代码---------------------

        if score_deduction < 1:  # 积分分值用负数
            df.at[idx, '体检表是否规范'] = '是'
        else:
            df.at[idx, '体检表是否规范'] = '否'

        df.at[idx, '积分分值'] = -score_deduction 
        df.at[idx, '核查结果汇总'] = ', '.join(set(results))
        df.at[idx, '核查现存健康问题'] = ', '.join(set(health_problem_issues))
        df.at[idx, '核查健康评价'] = ', '.join(set(health_eval_issues))
        df.at[idx, '核查健康指导'] = ', '.join(set(health_guide_issues))
        df.at[idx, '核查其他空错项'] = ', '.join(set(other_issues))     
    return df

def process_physical_exam():
    try:
        output_dir = "老年体检_核查结果"
        os.makedirs(output_dir, exist_ok=True)

        dtype_dict = {'个人健康档案号': str, '身份证号': str}
        df = pd.read_excel("老年体检_核查结果/体检明细表_类型已处理.xlsx", dtype=dtype_dict)
        df['体检日期'] = pd.to_datetime(df['体检日期'], errors='coerce').dt.strftime('%Y-%m-%d')
        record_count = len(df)

        df = check_physical_exam_data(df)

        print_step(3, "保存核查结果")
        output_path = os.path.join(output_dir, '体检明细表_已核查.xlsx')
        
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='体检明细表')
            
            workbook = writer.book
            worksheet = writer.sheets['体检明细表']
            
            for row_idx in range(2, len(df) + 2):
                cell = worksheet.cell(row=row_idx, column=df.columns.get_loc('个人健康档案号') + 1)
                cell.number_format = '@'
        return True, record_count  
    except Exception as e:
        print(f"处理过程中发生错误: {str(e)}")
        import traceback
        traceback.print_exc()
        return False, 0

def main():
    try:
        start_time = time.time()
        success, record_count = process_physical_exam()
        elapsed_time = time.time() - start_time

        if success:
            print_success("核查完成...")
        else:
            print("任务未能完成，请检查错误信息")
    except Exception as e:
        print(f"程序发生未预期的错误: {str(e)}")

if __name__ == "__main__":
    main()
############################################################################
#此部分用于填充村居名称，方便归村统计
import pandas as pd
import os

print("步骤4：将对体检表分村...")
resident_file = '居民档案.xlsx'
checkup_dir = '老年体检_核查结果'
checkup_file = os.path.join(checkup_dir, '体检明细表_已核查.xlsx')

try:
    resident_df = pd.read_excel(resident_file, engine='openpyxl', dtype={'身份证号': str})
    
    checkup_df = pd.read_excel(checkup_file,  engine='openpyxl',  dtype={'身份证号': str, '个人健康档案号': str})
    
    jurisdiction_map = resident_df.set_index('身份证号')['所属管辖'].to_dict()
    
    checkup_df['所属管辖'] = checkup_df['身份证号'].map(jurisdiction_map)
    
    matched_count = checkup_df['所属管辖'].notna().sum()
    unmatched_count = len(checkup_df) - matched_count
    print(f"  分村统计:")
    print(f"  成功分村: {matched_count} 条...")
    print(f"  无法分村: {unmatched_count} 条...")
    print( "  正在保存村居信息...")   
    
    with pd.ExcelWriter(checkup_file, engine='openpyxl') as writer:
        checkup_df.to_excel(writer, index=False)
    
    print(f"  ✅分村成功...")   
except FileNotFoundError as e:
    print(f"\n错误: 文件未找到 - {e}")
except Exception as e:
    print(f"\n发生错误: {e}")
#################################################################################################
#此部分用于统计各小项问题的数据，用于后期整改或列举问题
import pandas as pd
import os
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

def process_health_check_data():
    print( "步骤5：准备进行数据统计...")   
    input_path = "老年体检_核查结果/体检明细表_已核查.xlsx"
    df = pd.read_excel(input_path)
    
    df['所属管辖'] = df['所属管辖'].fillna('不详村居')
    
    df['核查结果数组'] = df['核查结果汇总'].str.split('[,，]')
    
    grouped = df.groupby('所属管辖')
    
    stats_data = []
    
    for name, group in grouped:
        total_count = len(group)
        standard_count = len(group[group['体检表是否规范'] == '是'])
        standard_percent = standard_count / total_count * 100 if total_count > 0 else 0
        
        score_99_count = len(group[group['积分分值'] > -1])
        score_99_percent = score_99_count / total_count * 100 if total_count > 0 else 0
        
        issue_counts = {}
        for issues in group['核查结果数组']:
            if isinstance(issues, list):
                for issue in issues:
                    if issue.strip():  # 排除空字符串
                        issue_counts[issue.strip()] = issue_counts.get(issue.strip(), 0) + 1
        
        stats_data.append({
            '所属管辖': name,
            '体检总人次数': total_count,
            '体检表是否规范=是': standard_count,
            '体检表是否规范=是_百分比': f"{standard_percent:.2f}%",
            '积分分值>=99人数': score_99_count,
            '积分分值>=99百分比': f"{score_99_percent:.2f}%",
            **issue_counts  # 将问题统计添加到结果中
        })
    
    stats_df = pd.DataFrame(stats_data)
    
    total_row = {
        '所属管辖': '合计',
        '体检总人次数': stats_df['体检总人次数'].sum(),
        '体检表是否规范=是': stats_df['体检表是否规范=是'].sum(),
        '积分分值>=99人数': stats_df['积分分值>=99人数'].sum()
    }
    
    total_standard_percent = total_row['体检表是否规范=是'] / total_row['体检总人次数'] * 100 if total_row['体检总人次数'] > 0 else 0
    total_score_percent = total_row['积分分值>=99人数'] / total_row['体检总人次数'] * 100 if total_row['体检总人次数'] > 0 else 0
    
    total_row['体检表是否规范=是_百分比'] = f"{total_standard_percent:.2f}%"
    total_row['积分分值>=99百分比'] = f"{total_score_percent:.2f}%"
    
    for col in stats_df.columns:
        if col not in total_row and stats_df[col].dtype in ['int64', 'float64']:
            total_row[col] = stats_df[col].sum()
    
    stats_df = pd.concat([stats_df, pd.DataFrame([total_row])], ignore_index=True)
    
    column_order = [
        '所属管辖', '体检总人次数', '体检表是否规范=是', '体检表是否规范=是_百分比', '积分分值>=99人数',
        '积分分值>=99百分比', '无法核查','联系电话','体检医生','症状','体温','脉率', '呼吸','血压','身高','体重','腰围','体指空',
        '锻炼','吸烟','饮酒','饮食','老年评估','职业危害',
        '视力','心率','口腔科','外科', '内科',
        '血Rt', '尿Rt','肝功', '血脂','肾功', '心电B超', '糖化空',
        '既往史','现存健康问题','评价乱码',
        '血压控制不满意/不达标漏评价', '血压多评价', '血压控制满意多评价','血压少评价', '血压危急漏评价',
        '血糖控制不满意/不达标漏评价', '血糖多评价', '血糖控制满意多评价', '空腹血糖受损漏评价', '血糖偏高漏评价', '血糖危急值漏评价', '糖化未评价',
        '多体指评价', '少体指评价', '少腹型肥胖', '无过缓', '脉心率差', '心电图漏评价', 'B超漏评价',
        '健康指导', '危险因素控制空',
        '多戒烟', '少戒烟','多健康饮酒', '少健康饮酒', '饮食多评价', '饮食少评价', '多锻炼', '少锻炼',
        '多减重', '少增重', '目标体重', '多减腰围', '少减腰围', '多防跌倒','少防跌倒'
    ]
    
    existing_columns = [col for col in column_order if col in stats_df.columns]
    other_columns = [col for col in stats_df.columns if col not in column_order]
    final_column_order = existing_columns + other_columns
    
    stats_df = stats_df[final_column_order]
    
    output_path = "老年体检_核查结果/体检明细表_统计表明细.xlsx"
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        stats_df.to_excel(writer, index=False, sheet_name='统计结果')
        
        workbook = writer.book
        worksheet = writer.sheets['统计结果']
        
        worksheet.column_dimensions['A'].width = 10
        worksheet.column_dimensions['B'].width = 6  
        worksheet.column_dimensions['C'].width = 6  
        worksheet.column_dimensions['D'].width = 6  
        worksheet.column_dimensions['E'].width = 6  
        worksheet.column_dimensions['F'].width = 6  
        for col in range(7, worksheet.max_column + 1):
            column_letter = get_column_letter(col)
            worksheet.column_dimensions[column_letter].width = 5 
        
        header_font = Font(color='FFFFFF', bold=True)  # 白色字体，加粗
        header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')  # 深蓝色背景
        
        for cell in worksheet[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(wrap_text=True, vertical='center', horizontal='center')
        
        worksheet.row_dimensions[1].height = 90
        
        for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
            for cell in row:
                if cell.column == 1:  # 第一列（所属管辖）左对齐
                    cell.alignment = Alignment(vertical='center')
                else:  # 其他列居中对齐
                    cell.alignment = Alignment(vertical='center', horizontal='center')
    
    print(f"  统计完成，结果已保存到: {output_path}")

process_health_check_data()
###################################################################################
#此部分用于汇总统计，按大类进行统计
import pandas as pd
import numpy as np
from openpyxl import load_workbook, Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from copy import copy

mapping_dict = {
    '所属管辖': ['所属管辖'],
    '体检总人次数': ['体检总人次数'],
    '规范数': ['体检表是否规范=是'],
    '规范率': ['体检表是否规范=是_百分比'],
    '分值>=99': ['积分分值>=99人数'],
    '分值>=99比': ['积分分值>=99百分比'],
    '无法核查':['无法核查'],
    '一般检查': [ '联系电话','体检医生','症状','体温','脉率', '呼吸','血压','身高','体重','腰围','体指空'],
    '老年评估': ['老年评估'],
    '生活方式': ['锻炼','吸烟','饮酒','饮食','职业危害'],
    '物理检查项': [ '视力','心率','口腔科','外科', '内科'],
    '生化检查': ['血Rt', '尿Rt','肝功', '血脂','肾功'],
    #'生化检查': ['血Rt', '尿Rt','肝功', '血脂','肾功', '糖化空'],
    '器械检查': ['心电B超'],
    '现存健康问题': ['既往史','现存健康问题'],
    '健康评价': [
        '评价乱码',
        '血压控制不满意/不达标漏评价', '血压多评价', '血压控制满意多评价','血压少评价', '血压危急漏评价',
        '血糖控制不满意/不达标漏评价', '血糖多评价', '血糖控制满意多评价', '空腹血糖受损漏评价', '血糖偏高漏评价', '血糖危急值漏评价', '糖化未评价',
        '多体指评价', '少体指评价', '少腹型肥胖', '无过缓', '脉心率差', '心电图漏评价', 'B超漏评价',
    ],
    '健康指导': ['健康指导'],
    '危险因素控制': [
        '危险因素控制空',
        '多戒烟', '少戒烟','多健康饮酒', '少健康饮酒', '饮食多评价', '饮食少评价', '多锻炼', '少锻炼',
        '多减重', '少增重', '目标体重', '多减腰围', '少减腰围', '多防跌倒','少防跌倒'
    ]
}

print( "步骤6：准备进行分项统计...")   
new_columns_order = list(mapping_dict.keys())

df_origin = pd.read_excel('老年体检_核查结果/体检明细表_统计表明细.xlsx')

percent_cols = ['体检表是否规范=是_百分比', '积分分值>=99百分比']
for col in percent_cols:
    if col in df_origin.columns:
        df_origin[col] = df_origin[col].str.rstrip('%').astype(float) / 100

new_df = pd.DataFrame()

for new_col in new_columns_order:
    src_cols = mapping_dict[new_col]
    
    if len(src_cols) == 1:
        src_col = src_cols[0]
        if src_col in df_origin.columns:
            new_df[new_col] = df_origin[src_col]
        else:
            new_df[new_col] = np.nan
    else:
        valid_cols = [col for col in src_cols if col in df_origin.columns]
        if valid_cols:
            new_df[new_col] = df_origin[valid_cols].sum(axis=1)
        else:
            new_df[new_col] = 0

df_village = new_df.iloc[:-1].copy()  # 村居数据（除最后一行）
df_total = new_df.iloc[[-1]].copy()   # 原合计行

recalc_total = df_village.sum(numeric_only=True)
recalc_total['所属管辖'] = '合计'
recalc_total['规范率'] = recalc_total['规范数'] / recalc_total['体检总人次数']
recalc_total['分值>=99比'] = recalc_total['分值>=99'] / recalc_total['体检总人次数']

final_df = pd.concat([df_village, recalc_total.to_frame().T], ignore_index=True)

output_path = '老年体检_核查结果/体检明细表_统计表.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    final_df.to_excel(writer, index=False, sheet_name='统计表')
    
    workbook = writer.book
    worksheet = writer.sheets['统计表']
    
    worksheet.column_dimensions['A'].width = 14  # 所属管辖列宽为14
    for col in range(2, worksheet.max_column + 1):
        column_letter = get_column_letter(col)
        worksheet.column_dimensions[column_letter].width = 9  # 其他列宽为7
    
    header_font = Font(color='FFFFFF', bold=True)  # 白色字体，加粗
    header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')  # 深蓝色背景
    
    for cell in worksheet[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(wrap_text=True, vertical='center', horizontal='center')
    
    worksheet.row_dimensions[1].height = 90
    
    for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
        for cell in row:
            if cell.column == 1:  # 第一列（所属管辖）左对齐
                cell.alignment = Alignment(vertical='center')
            else:  # 其他列居中对齐
                cell.alignment = Alignment(vertical='center', horizontal='center')
    
    for row in range(2, worksheet.max_row + 1):
        d_cell = worksheet.cell(row=row, column=4)
        if isinstance(d_cell.value, (int, float)):
            d_cell.number_format = '0.00%'
        
        f_cell = worksheet.cell(row=row, column=6)
        if isinstance(f_cell.value, (int, float)):
            f_cell.number_format = '0.00%'

print("  分项统计完成，已保存到:", output_path)
###################################################################################
#此部分转json，方便后期网页显示
import pandas as pd
import json
import os

print( "步骤7：对数据进行可视化编辑...")  
excel_path = "老年体检_核查结果/体检明细表_统计表.xlsx"
df = pd.read_excel(excel_path)

df = df[~df.apply(lambda row: row.astype(str).str.contains('合计').any(), axis=1)]
df = df.fillna(0)
# 转换为JSON格式
json_data = df.to_json(orient="records", force_ascii=False, indent=2)
# 保存JSON文件
json_path = "老年体检_核查结果/体检数据.json"
with open(json_path, "w", encoding="utf-8") as f:
    f.write(json_data)

####################################################################################
#此部分为替换真实数据
import json
import re

with open('standard/old_index.html', 'r', encoding='utf-8') as f:
    html_content = f.read()

with open('老年体检_核查结果/体检数据.json', 'r', encoding='utf-8') as f:
    json_data = json.load(f)

# 将JSON数据转换为JavaScript格式的字符串
js_data = json.dumps(json_data, ensure_ascii=False, indent=2)

pattern = r'originalData\s*=\s*\[.*?\];'
replacement = f'originalData = {js_data};'

new_html_content = re.sub(
    pattern, 
    replacement, 
    html_content, 
    count=1, 
    flags=re.DOTALL
)

with open('老年体检_核查结果/old_index.html', 'w', encoding='utf-8') as f:
    f.write(new_html_content)

print("")
print("  ✅文件已成功保存到：老年体检_核查结果/old_index.html")
#-----------------------生成第2个HTML文件
# with open('standard/old_index_在线.html', 'r', encoding='utf-8') as f:
#     html_content = f.read()

# with open('老年体检_核查结果/体检数据.json', 'r', encoding='utf-8') as f:
#     json_data = json.load(f)

# js_data = json.dumps(json_data, ensure_ascii=False, indent=2)
# pattern = r'originalData\s*=\s*\[.*?\];'
# replacement = f'originalData = {js_data};'
# new_html_content = re.sub(pattern, replacement, html_content, count=1,flags=re.DOTALL)
# with open('老年体检_核查结果/old_index_在线.html', 'w', encoding='utf-8') as f:
#     f.write(new_html_content)

# print("  ✅文件已成功保存到：老年体检_核查结果/old_index_在线.html")
###############################################################################
import shutil
import os
import webbrowser
source_folder = 'standard/css'
target_parent = '老年体检_核查结果'
target_folder = os.path.join(target_parent, 'css')

os.makedirs(target_parent, exist_ok=True)
shutil.copytree(src=source_folder,dst=target_folder,dirs_exist_ok=True)  

# 默认浏览器打开--------------------------
folder = "老年体检_核查结果"
filename = "old_index.html"
target_path = os.path.join(folder, filename)
if not os.path.exists(target_path):
    print(f"错误：文件 '{target_path}' 不存在")
else:
    abs_path = os.path.abspath(target_path)
    file_url = f"file:///{abs_path.replace(os.sep, '/')}"
    webbrowser.open(file_url)

print("=" * 80)
print("感谢你的使用")
print("           --郁进 13606278090 微信同号")
print("=" * 80)
